package com.foreveross.proxyip.infra.base.ddd.repository;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.foreveross.proxyip.infra.conf.HibernateConfigurationHelper;
/**
 * 
 * @author lyq
 *
 */
@Service
public class JdbcRepository  implements IJdbcRepository{
	// 一次批量插入数据库时，最大记录
	public static final Integer DEFAULT_BATCHSIZE = 1000;
	DataSource dataSource = null;

	JdbcTemplate jdbcTemplate = null;

	Map<String, String> insertSqlMap = new HashMap<String, String>();

	@Autowired
	public JdbcRepository(@Qualifier("dataSource") DataSource dataSource) {
		this.dataSource = dataSource;
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public DataSource getDataSource() {
		return dataSource;
	}


	public Connection getConnection() throws Exception{
		return dataSource.getConnection();
	}
	
	
	
	/**
	 * 获取jdbcTemplate
	 * @return
	 */
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	/**
	 * 执行一条sql
	 * @param conn
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public boolean execute(Connection conn ,String sql ) throws Exception{
		Statement state = conn.createStatement();
		return state.execute(sql);
	}

	/**
	 * 删除cla对应表的全部数据
	 * @param conn
	 * @param cla
	 * @throws Exception
	 */
	public void deleteAll(Connection conn ,Class clazz) throws Exception{
		Statement stat = conn.createStatement();
		String sql = generateDelSql(clazz);
		stat.executeUpdate(sql);
	}
	
	/**
	 * 在obj对应数据库表插入一条记录
	 * @param conn
	 * @param obj
	 * @throws Exception
	 */
	public void insert(Connection conn,Object obj) throws Exception{
		Class clazz = obj.getClass();
		String insertSql = this.generateInsertSql(clazz);
		PreparedStatement state = conn.prepareStatement(insertSql);
		this.buildStatement(state, obj);
		state.executeUpdate();
	}

	/**
	 * 
	 * @param <T>
	 * @param list 数据库持久对象
	 * @param clazz   clazz是数据库映射对象， 基本数据类型必须是一个对象如：integer
	 * @return
	 * @throws Exception
	 */
	public  int saveList(Connection conn ,List list, Class clazz) throws Exception {
		String insertSql = generateInsertSql(clazz);
		PreparedStatement pstat = null;
//		boolean currAutoCommit = true;
		try{
//			conn = dataSource.getConnection();
//			currAutoCommit = conn.getAutoCommit();
//			conn.setAutoCommit(false);
		pstat = conn.prepareStatement(insertSql);
		int count = 0, resultLength = 0;
		for (int i = 0; i < list.size(); i++) {
			Object obj = list.get(i);
			buildStatement(pstat,obj ) ;
			
				pstat.addBatch();
				if (++count % DEFAULT_BATCHSIZE == 0) {
					int[] intArray = pstat.executeBatch();
					if (intArray != null) {
						resultLength = resultLength + intArray.length;
					}
//				}
			}
		}
		int[] intArray = pstat.executeBatch();
		if (intArray != null) {
			resultLength = resultLength + intArray.length;
		}
		conn.commit();
		return resultLength;
		}catch(Exception e){
			e.printStackTrace();
			throw  e;
		}
//		finally{
//			if(pstat!=null){
//				pstat.close();
//			}
//			if(conn!=null){
//				conn.setAutoCommit(currAutoCommit);
//				conn.close();
//			}
//		}
	}

	private void buildStatement(PreparedStatement stmt, Object obj)
			throws Exception {
		Class clazz = obj.getClass();
		Field[] fields = clazz.getDeclaredFields();
		int i = 0;
		for (Field field : fields) {
			
			if(field.toGenericString().contains( " final ")){
				continue;
			}
			Class fcla = field.getType();
			Object value = getValue(field.getName(),obj);
			if (value != null) {
				if (byte[].class.equals(value.getClass())) {
					byte[] byteArray = (byte[]) value;
					stmt.setBytes(i + 1, byteArray);
				} else {
					if (Integer.class.equals(value.getClass())) {
						stmt.setInt(i + 1, ((Integer) value).intValue());
					}else if (Long.class.equals(value.getClass())) {
						stmt.setLong(i + 1, ((Long) value).longValue());
					}else{
						stmt.setObject(i + 1, value);
					}
				}
			} else {
				stmt.setNull(i + 1, this.getType(fcla));
			}
			i++;
		}
//		return true;
	}


	@SuppressWarnings("rawtypes")
	private String generateInsertSql(Class clazz) throws Exception {
		String insertSql = insertSqlMap.get(clazz.getSimpleName());
		if (StringUtils.isBlank(insertSql)) {
			StringBuffer sql = new StringBuffer();
			String tableName = HibernateConfigurationHelper.getTableName(clazz);
			sql.append("insert into ").append(tableName);
			StringBuffer signs = new StringBuffer();
			StringBuffer values = new StringBuffer();
			Field[] fields = clazz.getDeclaredFields();
			values.append(" (");
			signs.append(" (");
			for (Field field : fields) {
				if(field.toGenericString().contains(" final ")){
					continue;
				}
				String colName = HibernateConfigurationHelper.getColumnName(
						clazz, field.getName());
				if (StringUtils.isNotBlank(colName)) {
					values.append(colName).append(",");
					signs.append("?,");
				}
			}
			values = new StringBuffer(this.rmComma(values.toString()))
					.append(")");
			signs = new StringBuffer(this.rmComma(signs.toString()))
					.append(")");
			sql.append(values).append(" values").append(signs);
			insertSqlMap.put(clazz.getSimpleName(), sql.toString());
			insertSql = sql.toString();
		}
		return insertSql.toUpperCase().toString();
	}
	
	private String generateDelSql(Class cla) {
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(HibernateConfigurationHelper.getTableName(cla));
		return sql.toString();
	}
	
	

	private String rmComma(String str) {
		if (StringUtils.isBlank(str)) {
			return "";
		}
		str = StringUtils.trim(str);
		return StringUtils.removeEnd(str, ",");
	}

	@SuppressWarnings("rawtypes")
	private int getType(Class clz) {
		String clzName = clz.getSimpleName().toUpperCase();
		if (clzName.contains("INTEGER") || clzName.contains("LONG")) {
			return Types.INTEGER;
		} else if (clzName.contains("DATE")) {
			return Types.DATE;
		} else if (clzName.contains("CHAR") || clzName.contains("STRING")) {
			return Types.CHAR;
		} else if (clzName.contains("DOUBLE")) {
			return Types.DOUBLE;
		}
		return Types.CHAR;
	}
	
	@SuppressWarnings("unused")
	private Object getValue(String fieldName ,Object obj) throws Exception{
		return obj.getClass().getDeclaredMethod("get"+StringUtils.capitalize(fieldName)).invoke(obj);
	}

}
